import pymssql

import pymysql
import requests
import json
import re
import jieba
import pandas as pd
import numpy
from wordcloud import WordCloud, ImageColorGenerator
import matplotlib.pyplot as plt
from os import path
import numpy as np
from PIL import Image

# 连接本地sql server         地址          用户名   密码     数据库
# conn = pymssql.connect("192.168.117.121:1433", "sa", "bpr@doss@2015", "Test_BmsDB_V2_20190807")
conn = pymysql.connect("localhost", "root", "123456", "dolin", charset='utf8mb4' )
# 建立cursor
cursor = conn.cursor()
def get_comments():
    all_comments = ""
    # cursor.execute("select tam.Description from Tbl_Ass_MeetingResolution tam")
    # cursor.execute("SELECT tam.Description from Tbl_Ass_MeetingBacklog tam")
    cursor.execute("SELECT tb.content from tb_a_assess tb WHERE tb.goodsTableId =  16")
    list =  cursor.fetchall()
    print(list)
    for item in list:
        all_comments = all_comments+str(item)
    return all_comments
# 数据清洗处理模块
def data_clear():
    xt = get_comments()
    print(xt)
    pattern = re.compile(r'[\u4e00-\u9fa5]+')
    filedata = re.findall(pattern, xt)
    print(filedata)
    xx = ''.join(filedata)
    clear = jieba.lcut(xx)   # 切分词
    cleared = pd.DataFrame({'clear': clear})
    stopwords = pd.read_csv("chineseStopWords.txt", index_col=False, quoting=3, sep="\t", names=['stopword'], encoding='GBK')
    cleared = cleared[~cleared.clear.isin(stopwords.stopword)]

    count_words = cleared.groupby(by=['clear'])['clear'].agg(np.size)
    count_words = count_words.to_frame()
    count_words.columns = ['num']
    # count_words = cleared.groupby(by=['clear'])['clear'].agg({"num": numpy.size})
    count_words = count_words.reset_index().sort_values(by=["num"], ascending=False)
    return count_words

#词云展示模块
def make_wordclound():
    d = path.dirname(__file__)
    #模型的图像格式
    mask = np.array(Image.open(path.join(d, "model2.png"))) #,mask=mask
    wordcloud = WordCloud(font_path="simhei.ttf",mask=mask,
                          background_color="#EEEEEE",
                          max_font_size=150,width=1600,height=1200) #指定字体类型、字体大小和字体颜色
    word_frequence = {x[0]:x[1] for x in data_clear().head(200).values}
    wordcloud = wordcloud.fit_words(word_frequence)
    plt.imshow(wordcloud)
    plt.subplots_adjust(top=1,bottom=0,left=0,right=1,hspace=0,wspace=0) #铺满
    image_colors = ImageColorGenerator(mask) # 从背景图建立颜色方案
    wordcloud.recolor(color_func=image_colors) # 将词云颜色设置为背景图方案
    plt.axis("off")
    plt.show()

if __name__=="__main__":
    make_wordclound()
    print("finish")